﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using AlarmManagePlatform.Model;
using RLMP = RelationsLMP;

namespace AlarmManagePlatform.DataAccess
{
    internal class tb_AlarminstrumentHisData
    {
        internal void Add(List<Model.DB.tb_AlarminstrumentHisData> tb_AlarminstrumentHisDataList)
        {
            Relations.DBUtility.SqlServer.SqlDb sqlDb = null;
            try
            {
                string sqlScript, currDateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                sqlDb = Static.Methods.OpenDataBase();
                foreach (Model.DB.tb_AlarminstrumentHisData tb_AlarminstrumentHisData in tb_AlarminstrumentHisDataList)
                {
                    sqlScript = string.Format("Insert Into tb_AlarminstrumentHisData (InstrumentId,IndexId,VerificationItemId,ParaId,ParaValue,TestValue,UserId,CreateDate)" +
                    " values ('{0}',{1},{2},{3},'{4}',{5},{6},'{7}');", tb_AlarminstrumentHisData.InstrumentId, tb_AlarminstrumentHisData.IndexId, tb_AlarminstrumentHisData.VerificationItemId
                    , tb_AlarminstrumentHisData.ParaId, tb_AlarminstrumentHisData.ParaValue, tb_AlarminstrumentHisData.TestValue, tb_AlarminstrumentHisData.UserId, currDateTime);
                    sqlDb.UpdateSqlScript(sqlScript);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("保存检测历史数据失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (sqlDb != null)
                    Static.Methods.CloseDataBase(sqlDb);
            }
        }

        internal List<Model.DB.tb_AlarminstrumentHisData> getAlarminstrumentHisData(string InstrumentId, string collectDate)
        {
            Relations.DBUtility.SqlServer.SqlDb sqlDb = null;
            try
            {
                List<Model.DB.tb_AlarminstrumentHisData> result = new List<Model.DB.tb_AlarminstrumentHisData>();
                Model.DB.tb_AlarminstrumentHisData _Temp;
                string sqlScript = string.Format("select Id,DCTid,InstrumentId,IndexId,VerificationItemId,ParaId,PavaValue,TestValue,UserId,CreateDate from tb_AlarminstrumentHisData where InstrumentId = '{0}' and CreateDate = '{1}';"
                    , InstrumentId, collectDate);
                DataTable dataTable;
                sqlDb = Static.Methods.OpenDataBase();
                dataTable = sqlDb.RunSqlScript(sqlScript);
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {

                    _Temp = new Model.DB.tb_AlarminstrumentHisData();

                    _Temp.IndexId = int.Parse(dataTable.Rows[i]["IndexId"].ToString());
                    _Temp.DCTid = dataTable.Rows[i]["DCTid"].ToString();
                    _Temp.InstrumentId = dataTable.Rows[i]["InstrumentId"].ToString();
                    _Temp.ParaId = int.Parse(dataTable.Rows[i]["ParaId"].ToString());
                    _Temp.ParaValue = dataTable.Rows[i]["ParaValue"].ToString();
                    _Temp.TestValue = float.Parse(dataTable.Rows[i]["TestValue"].ToString());
                    _Temp.UserId = int.Parse(dataTable.Rows[i]["UserId"].ToString());
                    _Temp.VerificationItemId = int.Parse(dataTable.Rows[i]["VerificationItemId"].ToString());

                    //_Temp.CreateDate = DateTime.Parse(dataTable.Rows[i]["CreateDate"].ToString()),

                    if (!string.IsNullOrEmpty(dataTable.Rows[i]["CreateDate"].ToString()))
                        _Temp.CreateDate = DateTime.Parse(dataTable.Rows[i]["CreateDate"].ToString()).ToString(Static.ParaInfo.DateFormatLong);

                    result.Add(_Temp);
                }


                return result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取检测历史数据失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (sqlDb != null)
                    Static.Methods.CloseDataBase(sqlDb);
            }
        }

        internal List<RLMP.Model.ReportInfo> GetReportInfo(int startIndex, int searchCont, Dictionary<string, string> logicList)
        {

            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            List<RLMP.Model.ReportInfo> _Result = new List<RLMP.Model.ReportInfo>();
            RLMP.Model.ReportInfo _TempReportInfo = null;
            Model.DB.tb_User _User;
            Model.DB.tb_AlarminstrumentInfo _AlarminstrumentInfo;
            DataTable _DataTable;
            string _SqlScript;
            try
            {
                _Result = new List<RLMP.Model.ReportInfo>();

                _SqlScript = $"Select Distinct Top({searchCont}) InstrumentId,UserId,CreateDate From tb_AlarminstrumentHisData ";
                _SqlScript += "     Where ID > 0";

                if (logicList.Count > 0)
                {
                    if (!string.IsNullOrEmpty(logicList["BeginTime"]))
                    {
                        _SqlScript += $"   And CreateDate >= '{logicList["BeginTime"]}'";
                    }

                    if (!string.IsNullOrEmpty(logicList["FinishTime"]))
                    {
                        _SqlScript += $"   And CreateDate <= '{logicList["FinishTime"]}'";
                    }

                    if (!string.IsNullOrEmpty(logicList["UserId"]))
                    {
                        _SqlScript += $"   And UserId = {logicList["UserId"]}";
                    }
                }

                _SqlScript += "     And CreateDate Not in (";

                #region 查询创建时间
                _SqlScript += $"Select Distinct Top({startIndex}) CreateDate From tb_AlarminstrumentHisData ";
                _SqlScript += "     Where ID > 0";

                if (logicList.Count > 0)
                {
                    if (!string.IsNullOrEmpty(logicList["BeginTime"]))
                    {
                        _SqlScript += $"   And CreateDate >= '{logicList["BeginTime"]}'";
                    }

                    if (!string.IsNullOrEmpty(logicList["BeginTime"]))
                    {
                        _SqlScript += $"   And CreateDate <= '{logicList["FinishTime"]}'";
                    }

                    if (!string.IsNullOrEmpty(logicList["UserId"]))
                    {
                        _SqlScript += $"   And UserId = {logicList["UserId"]}";
                    }
                }

                _SqlScript += "     Order By CreateDate";

                _SqlScript += "     )";
                _SqlScript += "     Order By CreateDate";

                #endregion 查询创建时间

                _SqlDb = Static.Methods.OpenDataBase();
                _DataTable = _SqlDb.RunSqlScript(_SqlScript);

                for (int i = 0; i < _DataTable.Rows.Count; i++)
                {
                    _TempReportInfo = new RLMP.Model.ReportInfo();
                    _User = new DataAccess.tb_User().GetForUserId(Convert.ToInt32(_DataTable.Rows[i]["UserId"].ToString()));
                    _AlarminstrumentInfo = new DataAccess.tb_AlarminstrumentInfo().GetInstrumentForInstrumentIdSingle(_DataTable.Rows[i]["InstrumentId"].ToString());
                    _Result.Add(new RLMP.Model.ReportInfo()
                    {
                        Model = _AlarminstrumentInfo == null ? "未知" : _AlarminstrumentInfo.Model,
                        FactoryNumber = _AlarminstrumentInfo.FactoryNumber,
                        VerificationOperator = _User == null ? "未知" : _User.userName,
                        CheckedTime = _DataTable.Rows[i]["CreateDate"].ToString()
                    });

                    //_Result.Add(_TempReportInfo);
                }
                return _Result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取报表信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal List<Model.HisCreateTimeInfo> GetInstrumentHisDataList(string instrumentId)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            List<Model.HisCreateTimeInfo> _Result;
            Model.HisCreateTimeInfo hisCreateTimeInfo = null;
            DataTable _DataTable;
            string _SqlScript;
            try
            {
                _Result = new List<HisCreateTimeInfo>();

                _SqlScript = $"Select CreateDate From tb_AlarminstrumentHisData Where InstrumentId = '{instrumentId}' Group by CreateDate Order By CreateDate";
                _SqlDb = Static.Methods.OpenDataBase();
                _DataTable = _SqlDb.RunSqlScript(_SqlScript);

                for (int i = 0; i < _DataTable.Rows.Count; i++)
                {
                    hisCreateTimeInfo = new HisCreateTimeInfo();
                    hisCreateTimeInfo.CreateTime = DateTime.Parse(_DataTable.Rows[i]["CreateDate"].ToString()).ToString(Static.ParaInfo.DateFormatLong);
                    hisCreateTimeInfo.ParaInfo = new DataAccess.tb_AlarminstrumentHisDeailData().GetParaInfo(instrumentId, hisCreateTimeInfo.CreateTime);

                    _Result.Add(hisCreateTimeInfo);
                }
                return _Result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取报警器详细信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        #region 历史数据左侧树形图
        internal int GetInstrumentHisDataListCount(string instrumentId, Dictionary<string, string> logicList)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            DataTable _DT = new DataTable();
            string _SqlScript;
            try
            {
                _SqlScript = $"Select Distinct CreateDate From tb_AlarminstrumentHisData Where InstrumentId = '{instrumentId}'";

                if (logicList.ContainsKey("BeginTime") && !string.IsNullOrEmpty(logicList["BeginTime"]))
                {
                    _SqlScript += $"   And CreateDate >= '{logicList["BeginTime"]}'";
                }

                if (logicList.ContainsKey("FinishTime") && !string.IsNullOrEmpty(logicList["FinishTime"]))
                {
                    _SqlScript += $"   And CreateDate <= '{logicList["FinishTime"]}'";
                }

                if (logicList.ContainsKey("UserId") && !string.IsNullOrEmpty(logicList["UserId"]))
                {
                    _SqlScript += $"   And UserId = {logicList["UserId"]}";
                }

                _SqlScript += $"    Order By CreateDate";
                _SqlDb = Static.Methods.OpenDataBase();

                _DT = _SqlDb.RunSqlScript(_SqlScript);

                return _DT.Rows.Count;
            }
            catch (Exception ex)
            {
                throw new Exception("获取历史数据数量失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal List<Model.HisCreateTimeInfo> GetInstrumentHisDataList(string instrumentId, int startIndex, int searchCount, Dictionary<string, string> logicList)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            List<Model.HisCreateTimeInfo> _Result;
            Model.HisCreateTimeInfo hisCreateTimeInfo = null;
            DataTable _DataTable;
            string _SqlScript;
            try
            {
                _Result = new List<HisCreateTimeInfo>();

                //_SqlScript = $"Select Top {searchCount} CreateDate From tb_AlarminstrumentHisData";
                //_SqlScript += $"     Where  InstrumentId = '{instrumentId}' And  CreateDate Not in ( Select Top {startIndex} CreateDate From tb_AlarminstrumentHisData  Where  InstrumentId = '{instrumentId}' Group by CreateDate Order By CreateDate )";
                //_SqlScript += "    Group by CreateDate Order By CreateDate Desc";


                _SqlScript = "      SELECT * FROM (SELECT  *, ROW_NUMBER() OVER ( ORDER BY [CreateDate] Desc) AS Num FROM ( SELECT Distinct CreateDate FROM tb_AlarminstrumentHisData ";


                _SqlScript += $"    Where  [InstrumentId] = '{instrumentId}'";

                if (logicList.ContainsKey("BeginTime") && !string.IsNullOrEmpty(logicList["BeginTime"]))
                {
                    _SqlScript += $"   And CreateDate >= '{logicList["BeginTime"]}'";
                }

                if (logicList.ContainsKey("FinishTime") && !string.IsNullOrEmpty(logicList["FinishTime"]))
                {
                    _SqlScript += $"   And CreateDate <= '{logicList["FinishTime"]}'";
                }

                if (logicList.ContainsKey("UserId") && !string.IsNullOrEmpty(logicList["UserId"]))
                {
                    _SqlScript += $"   And UserId = {logicList["UserId"]}";
                }

                _SqlScript += $"    ) As TempTable1  )AS TempTable";
                _SqlScript += $"    WHERE Num BETWEEN {startIndex} AND {startIndex + searchCount - 1}";


                _SqlDb = Static.Methods.OpenDataBase();
                _DataTable = _SqlDb.RunSqlScript(_SqlScript);

                for (int i = 0; i < _DataTable.Rows.Count; i++)
                {
                    hisCreateTimeInfo = new HisCreateTimeInfo();
                    hisCreateTimeInfo.CreateTime = DateTime.Parse(_DataTable.Rows[i]["CreateDate"].ToString()).ToString(Static.ParaInfo.DateFormatLong);
                    hisCreateTimeInfo.ParaInfo = new DataAccess.tb_AlarminstrumentHisDeailData().GetParaInfo(instrumentId, hisCreateTimeInfo.CreateTime);

                    _Result.Add(hisCreateTimeInfo);
                }
                return _Result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取报警器详细信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        #endregion 历史数据左侧树形图

        internal List<Model.CheckDataInfo> GetCheckDataInfo(string instrumentId, string createTime, List<RelationsLMP.Model.InstrumentParaInfo> instrumentParaInfoList)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            List<Model.CheckDataInfo> _Result;
            Model.CheckDataInfo _CheckDataInfo = null;
            Model.DB.tb_ParaInfo tb_ParaInfo;
            DataTable _DataTable;
            string _SqlScript;

            int _ParaIndex, _ParaId;
            try
            {
                _Result = new List<CheckDataInfo>();
                _ParaIndex = 0;
                _SqlScript = $"Select * From tb_AlarminstrumentHisData Where InstrumentId = '{instrumentId}' and CreateDate = '{createTime}' Order By IndexId Asc, ParaId Asc";
                _SqlDb = Static.Methods.OpenDataBase();
                _DataTable = _SqlDb.RunSqlScript(_SqlScript);

                for (int i = 0; i < _DataTable.Rows.Count; i++)
                {
                    _ParaId = int.Parse(_DataTable.Rows[i]["ParaId"].ToString());
                    if (_ParaIndex >= instrumentParaInfoList.Count)
                        _ParaIndex = 0;
                    if (instrumentParaInfoList[_ParaIndex].ParaId == _ParaId)
                    {
                        _CheckDataInfo = new Model.CheckDataInfo();

                        _CheckDataInfo.IndexId = int.Parse(_DataTable.Rows[i]["IndexId"].ToString());
                        _CheckDataInfo.UserId = _DataTable.Rows[i]["UserId"].ToString();
                        tb_ParaInfo = new DataAccess.tb_ParaInfo().GetParaInfo(_ParaId.ToString());
                        _CheckDataInfo.tb_ParaInfo = tb_ParaInfo;
                        _CheckDataInfo.Unit = string.Empty;
                        _CheckDataInfo.ParaValue = _DataTable.Rows[i]["ParaValue"].ToString();
                        _CheckDataInfo.CreateDate = _DataTable.Rows[i]["CreateDate"].ToString();
                        _CheckDataInfo.Verificationitemid = (Model.SystemEnum.VerificationItem)int.Parse(_DataTable.Rows[i]["VerificationItemId"].ToString());
                        _CheckDataInfo.TestValue = Relations.Common.Methods.IsFloat(_DataTable.Rows[i]["TestValue"].ToString()) ? float.Parse(_DataTable.Rows[i]["TestValue"].ToString()) : 0;
                        _Result.Add(_CheckDataInfo);
                    }
                    _ParaIndex++;
                }
                return _Result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取被检仪器历史数据失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal void DeleteAlarminstrumentHis(List<string> listInstrumentId)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            string _InstrumentIds = string.Empty;
            string _SqlScript = string.Empty;
            try
            {
                _SqlDb = Static.Methods.OpenDataBase();
                _InstrumentIds = Static.Methods.CombiningStrings1<string>(listInstrumentId);

                _SqlScript = "Delete From tb_AlarminstrumentHisData";
                _SqlScript += $" Where InstrumentId in ( {_InstrumentIds} )";

                _SqlDb.UpdateSqlScript(_SqlScript);
            }
            catch (Exception ex)
            {
                throw new Exception("删除被检仪器历史数据失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }
        internal void DeleteAlarminstrumentHis(string instrumenrtId, string createTime)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            string _SqlScript = string.Empty;
            try
            {
                _SqlDb = Static.Methods.OpenDataBase();

                _SqlScript = "Delete From tb_AlarminstrumentHisData";
                _SqlScript += $" Where InstrumentId = '{instrumenrtId}' And CreateDate = '{createTime}'";

                _SqlDb.UpdateSqlScript(_SqlScript);
            }
            catch (Exception ex)
            {
                throw new Exception("获取删除检测历史详细数据失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }


        /// <summary>
        /// 报表统计用
        /// </summary>
        /// <param name="logicList"></param>
        /// <returns></returns>
        internal int GetInstrumentHisDataListCount(Dictionary<string, string> logicList)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            int _Result;
            string _Saclar = string.Empty;
            string _SqlScript;
            try
            {
                _Result = -1;

                _SqlScript = $"Select Count(Distinct CreateDate) From tb_AlarminstrumentHisData ";
                _SqlScript += "     Where ID > 0";

                if (logicList.ContainsKey("BeginTime") && !string.IsNullOrEmpty(logicList["BeginTime"]))
                {
                    _SqlScript += $"   And CreateDate >= '{logicList["BeginTime"]}'";
                }

                if (logicList.ContainsKey("FinishTime") && !string.IsNullOrEmpty(logicList["FinishTime"]))
                {
                    _SqlScript += $"   And CreateDate <= '{logicList["FinishTime"]}'";
                }

                if (logicList.ContainsKey("UserId") && !string.IsNullOrEmpty(logicList["UserId"]))
                {
                    _SqlScript += $"   And UserId = {logicList["UserId"]}";
                }

                _SqlDb = Static.Methods.OpenDataBase();

                _Saclar = _SqlDb.RunSqlScriptReturnScalar(_SqlScript);
                if (string.IsNullOrEmpty(_Saclar))
                {
                    _Result = 0;
                }
                else
                {
                    _Result = Convert.ToInt32(_Saclar);
                }

                return _Result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取检测历史数据数量失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }
    }
}